/*
   Copyright 2010 Johan Hilding

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

     http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

package org.johanhil.flygtider.flightwatch;

import org.johanhil.flygtider.provider.FlightInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Most of this is borrowed from Johan Nilsson's code at http://github.com/johannilsson/sthlmtraveling/blob/master/src/com/markupartist/sthlmtraveling/FavoritesActivity.java
 *
 */
public class FlightWatchDatabaseAdapter {
    public static final String KEY_ROWID = "_id";
    public static final String KEY_DATE = "date";
    public static final String KEY_DEPARTING_AIRPORT = "dep_airport";
    public static final String KEY_ARRIVING_AIRPORT = "arr_airport";
    public static final String KEY_ARRIVING = "arriving"; // used when we search for updates.
    public static final String KEY_BEST_KNOWN_TIME = "best_time"; // used to determine when to look for updates
    public static final String KEY_FLIGHT_NUMBER = "flightnr";
    public static final String KEY_NOTE = "note";

    private static final String DATABASE_NAME = "watched_flights";
    private static final String DATABASE_TABLE = "watched_flights";
    private static final int DATABASE_VERSION = 1;

    private DatabaseHelper databaseHelper;
    private SQLiteDatabase database;
    private final Context context;	
    
    private static final String TAG = "FlightWatchDatabaseAdapter";

    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE =
            "CREATE TABLE " + DATABASE_TABLE + "(" 
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT"
                + ", " + KEY_DATE + " TEXT NOT NULL" 
                + ", " + KEY_BEST_KNOWN_TIME + " TEXT NOT NULL"
                + ", " + KEY_DEPARTING_AIRPORT + " TEXT NOT NULL"
                + ", " + KEY_ARRIVING_AIRPORT + " TEXT NOT NULL"
                + ", " + KEY_FLIGHT_NUMBER + " TEXT NOT NULL"
                + ", " + KEY_ARRIVING + " BOOLEAN NOT NULL"
                + ", " + KEY_NOTE + " TEXT NOT NULL"
                + ");";

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param context the Context within which to work
     */
    public FlightWatchDatabaseAdapter(Context context) {
        this.context = context;
    }

    /**
     * Open the watched flights' database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public FlightWatchDatabaseAdapter open() throws SQLException {
        databaseHelper = new DatabaseHelper(context);
        database = databaseHelper.getWritableDatabase();
        return this;
    }

    /**
     * Close any open database connection.
     */
    public void close() {
    	databaseHelper.close();
    }

    /**
     * Creates a new entry. If the entry already exists it will be updated
     * with a new note.
     * @param flightInfo The FlightInfo object associated with the flight.
     * @return the row id associated with the created entry or -1 of an error
     * occurred
     */
    public long add(FlightInfo flightInfo) {
    	if (flightInfo == null)
    	{
    		return -1;
    	}
    	
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_DATE, flightInfo.getDate());
        initialValues.put(KEY_DEPARTING_AIRPORT, flightInfo.getDepartingAirport());
        initialValues.put(KEY_ARRIVING_AIRPORT, flightInfo.getArrivingAirport());
        initialValues.put(KEY_ARRIVING, flightInfo.isArriving());
        initialValues.put(KEY_FLIGHT_NUMBER, flightInfo.getFlightNr());
        initialValues.put(KEY_NOTE, flightInfo.getNotes());
        initialValues.put(KEY_BEST_KNOWN_TIME, flightInfo.getTime());

        // makes updating possible
        Cursor rowCursor = fetchByFlightInfo(flightInfo);
        if (rowCursor.getCount() >= 1) {
            initialValues.put(KEY_ROWID, 
                    rowCursor.getInt(rowCursor.getColumnIndex(KEY_ROWID)));
        }
        rowCursor.close();

        return database.replace(DATABASE_TABLE, null, initialValues);
    }

    /**
     * Only uses date + flight#.
     * @param type the FlightInfo object
     * @return a Cursor object positioned at the first entry
     */
    public Cursor fetchByFlightInfo(FlightInfo flightInfo) 
    {
        Cursor cursor =
            database.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_DATE, KEY_FLIGHT_NUMBER}, KEY_DATE + "=\"" + flightInfo.getDate() + "\"" 
                        + " AND " + KEY_FLIGHT_NUMBER + "=\"" + flightInfo.getFlightNr() + "\"", 
                    null, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
    public void deleteByFlightInfo(FlightInfo flightInfo)
    {
    	database.delete(DATABASE_TABLE, KEY_DATE + "=\"" + flightInfo.getDate() + "\"" 
                + " AND " + KEY_FLIGHT_NUMBER + "=\"" + flightInfo.getFlightNr() + "\"", null);
    	
    }

    /**
     * Fetch all watched flights.
     * @param type the type
     * @return a Cursor object
     */
    public Cursor fetchAll() {
        Cursor mCursor =
            database.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_DATE, KEY_DEPARTING_AIRPORT, KEY_ARRIVING_AIRPORT, KEY_FLIGHT_NUMBER, KEY_ARRIVING, KEY_NOTE, KEY_BEST_KNOWN_TIME}, null, null,
                    null, null, null, null);
        return mCursor;        
    }

    /**
     * Internal helper for the database.
     */
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
            onCreate(db);
        }
    }
}